#!/bin/sh

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=$(date -d "-1 days" +%F)
fi

sql="
add jar /home/bwie/jars/zg6_yuekao-1.0-SNAPSHOT.jar;
create temporary function geoudf as 'com.bw.Myudf';

with a1 as (
    select
        a2.id,
        a1.areaname province,
        a2.areaname city
    from jtp_app_ods.ods_md_areas a1
             left join jtp_app_ods.ods_md_areas a2 on a1.id=a2.parentid
),a2 as (
    select
        a3.id,
        a1.province,
        a1.city,
        a3.areaname district
    from a1 left join jtp_app_ods.ods_md_areas a3 on a1.id=a3.parentid
),a3 as (
    select
        a4.id,
        a2.province,
        a2.city,
        a2.district,
        a4.areaname street,
        a4.bd09_lng,
        a4.bd09_lat,
        geoudf(a4.bd09_lng,a4.bd09_lat,6) geo_hase
    from a2 left join jtp_app_ods.ods_md_areas a4 on a2.id=a4.parentid
    where a4.bd09_lat is not null
      and a4.bd09_lng is not null
)insert into table jtp_app_dim.dim_md_areas partition (dt='${data_date}')
select * from a3;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "$sql"